﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLiNhaSachDTO;

namespace QuanLiNhaSachDAO
{
    public class TacGiaDAO
    {
        public static DataTable getByMaSach(int MaSach)
        {
            string sql = "SELECT SACHTACGIA.id, SACHTACGIA.MaSach, SACHTACGIA.MaTacGia, SACHTACGIA.id AS Expr1, TACGIA.Ten FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = " + MaSach + ")";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable selectAll()
        {
            string sql = "SELECT * FROM TACGIA";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static void insert(TacGia m)
        {
            string sql = "INSERT INTO TACGIA(Ten,Email,DienThoai,DiaChi,GhiChu) VALUES(@Ten,@Email,@DienThoai,@DiaChi,@GhiChu)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = sqlDB.MyConnection;
            cmd.Parameters.AddWithValue("@Ten", m.Ten);
            cmd.Parameters.AddWithValue("@Email", m.Email);
            cmd.Parameters.AddWithValue("@DienThoai", m.DienThoai);
            cmd.Parameters.AddWithValue("@DiaChi", m.DiaChi);
            cmd.Parameters.AddWithValue("@GhiChu", m.GhiChu);
            cmd.ExecuteNonQuery();
        }

        public static void update(TacGia m)
        {
            string sql = "UPDATE TACGIA SET Ten = @Ten, Email = @Email, DienThoai = @DienThoai, DiaChi = @DiaChi, GhiChu = @GhiChu WHERE id = @id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = sqlDB.MyConnection;
            cmd.Parameters.AddWithValue("@id", m.Id);
            cmd.Parameters.AddWithValue("@Ten", m.Ten);
            cmd.Parameters.AddWithValue("@Email", m.Email);
            cmd.Parameters.AddWithValue("@DienThoai", m.DienThoai);
            cmd.Parameters.AddWithValue("@DiaChi", m.DiaChi);
            cmd.Parameters.AddWithValue("@GhiChu", m.GhiChu);
            cmd.ExecuteNonQuery();
        }

        public static void delete(string id)
        {
            string sql = "DELETE FROM TACGIA WHERE id = @id";
            SqlCommand cmd = new SqlCommand(sql, sqlDB.MyConnection);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
        }

        public static DataTable search(string id)
        {
            string sql = "SELECT * FROM TACGIA WHERE id ='" + id + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable TimSachTuTacGia(string id)
        {
            string sql = "SELECT * FROM SACH WHERE MaTacGia = '" + id + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static string TimTacGia(string id)
        {
            string sql = "SELECT TACGIA.Ten FROM SACH INNER JOIN SACHTACGIA ON SACH.id = SACHTACGIA.MaSach ";
            sql += "INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id ";
            sql += "WHERE SACH.id = " + id + "";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string tmpStr = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tmpStr += dt.Rows[i][0].ToString() + " & ";
            }
            int dodai = tmpStr.Length;
            tmpStr = tmpStr.Remove(dodai - 3);
            return tmpStr;
        }
    }
}
